package cn.pugle.ormtest.hibernate.repository;

import cn.pugle.ormtest.hibernate.dto.OrderDetail;
import cn.pugle.ormtest.hibernate.model.OrderModel3;
import cn.pugle.ormtest.hibernate.repository.mixin.MyBaseRepository;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.hibernate.type.DateType;
import org.hibernate.type.LongType;
import org.springframework.data.domain.Example;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;

import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.Date;
import java.util.List;

/**
 * @author tzp
 * @since 2017/12/26
 */
public interface OrderRepository extends JpaRepository<OrderModel3, Long>, MyBaseRepository {

    List<OrderModel3> findByCustomerId(Long customerId);

    @Modifying
    void deleteById(long id);


    /**
     * 测试执行sql和default
     * sql to list<Object> / sql to List<Entity>
     */
    default List<OrderModel3> recentOrders() {
        LocalDateTime recent_ = LocalDateTime.now().minusDays(7);
        Date recent = Date.from(recent_.atZone(ZoneId.systemDefault()).toInstant());

        //jpa entityManager的这个查询没法做结果映射
        List listOfObject = getEntityManager().createNativeQuery(
                "SELECT id,order_date,price,address FROM st_order3 WHERE order_date >= :recent")
                .setParameter("recent", recent)
                .getResultList();

        //hibernate支持结果映射
        Session session = getEntityManager().unwrap(Session.class);//需要开启事务
        List orders = session
                //createNativeQuery(String var1, Class<R> var2);
                .createNativeQuery("SELECT o.*" +
                        " FROM st_order3 o " +
                        " WHERE o.order_date >= :recent")
                .addEntity("o", OrderModel3.class)
                .setParameter("recent", recent)
                .getResultList();//结果list里是OrderModels

        //这个写法在用order的customer时就不用再查n次了. 不过结果不是OrderModels. 是 arr[n][2], 里面装了俩model
        List arrayarray = session
                //createNativeQuery(String var1, Class<R> var2);
                .createNativeQuery("SELECT o.*, c.*" +
                        " FROM st_order3 o left join st_customer3 c on c.id = o.customer_id " +
                        " WHERE o.order_date >= :recent")
                .addEntity("o", OrderModel3.class)
                .addJoin("c", "o.customer")
                .setParameter("recent", recent)
                .getResultList();

        return orders;
    }

    /**
     * sql to dto
     * many-to-one
     * 特别蠢, select的要把列名as成和dto名一样的才行, 感觉写的比映射出list<Object>还烦.
     * 结果里id还是integer,dto是long还报错,addScalar一个就得都add, 否则查出来了也不放入dto
     * 果然在java里出现了超长的sql
     */
    default List<OrderDetail> recentOrderDetails() {
        LocalDateTime recent_ = LocalDateTime.now().minusDays(7);
        Date recent = Date.from(recent_.atZone(ZoneId.systemDefault()).toInstant());

        Session session = getEntityManager().unwrap(Session.class);
        List orders = session
                .createNativeQuery("SELECT o.id as id, o.order_date as orderDate, o.price as price," +
                        " o.address as address, c.name as customerName" +
                        " FROM st_order3 o left join st_customer3 c on c.id = o.customer_id " +
                        " WHERE o.order_date >= :recent")
                .setParameter("recent", recent)
                .addScalar("id", LongType.INSTANCE)
                .addScalar("orderDate", DateType.INSTANCE)
                .addScalar("price", LongType.INSTANCE)
                .addScalar("address")
                .addScalar("customerName")
                .setResultTransformer(Transformers.aliasToBean(OrderDetail.class))//这个转dto太特么蠢了, 怪不得废弃了
                .list();

        return orders;
    }

}
